﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Globalization;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class panel_MaterialImportPlan : UserControl
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        private DataTable PlanTable;
        private DataTable SubPlanTable;
        private DataTable StructureTable;
        private DataTable SumTable;
        private DataTable OrderTable;

        private int state;

        public panel_MaterialImportPlan()
        {
            InitializeComponent();

            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);

            this.PlanTable = new DataTable();
            this.PlanTable.Columns.Add("ID", typeof(int));
            this.PlanTable.Columns.Add("CompID", typeof(string));            
            this.PlanTable.Columns.Add("Quantity", typeof(float));
            this.PlanTable.Columns.Add("DMonth", typeof(DateTime));

            this.SubPlanTable = new DataTable();
            this.SubPlanTable.Columns.Add("ID", typeof(int));
            this.SubPlanTable.Columns.Add("CompID", typeof(string));
            this.SubPlanTable.Columns.Add("Quantity", typeof(float));
            this.SubPlanTable.Columns.Add("DMonth", typeof(DateTime));

            this.StructureTable = new DataTable();
            this.StructureTable.Columns.Add("MaterialID", typeof(string));
            this.StructureTable.Columns.Add("Quantity", typeof(float));

            this.SumTable = new DataTable();
            this.SumTable.Columns.Add("MaterialID", typeof(string));
            this.SumTable.Columns.Add("Quantity", typeof(float));
            
            this.OrderTable = new DataTable();
            this.OrderTable.Columns.Add("PartNo", typeof(string));
            this.OrderTable.Columns.Add("PartNoPurchase", typeof(string));
            this.OrderTable.Columns.Add("PartName", typeof(string));
            this.OrderTable.Columns.Add("Supplier", typeof(string));
            this.OrderTable.Columns.Add("NeedQuantity", typeof(float));
            this.OrderTable.Columns.Add("RemainQuantity", typeof(float));
            this.OrderTable.Columns.Add("InOrderQuantity", typeof(float));            
            this.OrderTable.Columns.Add("UnitID", typeof(string));
            this.OrderTable.Columns.Add("LeadTime", typeof(int));
            this.OrderTable.Columns.Add("PackingNote", typeof(string));
            this.OrderTable.Columns.Add("Remark", typeof(string));
            this.OrderTable.Columns.Add("MOQ", typeof(float));
            this.OrderTable.Columns.Add("Marume", typeof(float));
            this.OrderTable.Columns.Add("RoHS", typeof(bool));                      

            this.state = 0;
            
            LoadCompList();
            LoadData();
        }

        ///////////////////////////////////////
        /// Load Data
        /////////////////////////////////////
        private void LoadData()
        {
            this.PlanTable.Rows.Clear();
            DateTime frommonth = new DateTime();
            DateTime tomonth = new DateTime();
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                if (string.IsNullOrEmpty(this.fromMonth.EditValue.ToString()))
                {
                    if (string.IsNullOrEmpty(this.toMonth.EditValue.ToString()))
                    {
                        sql_cmd.CommandText = "SELECT * FROM [444_MaterialPlan] ORDER BY [Dmonth] DESC";
                    }
                    else
                    {
                        tomonth = this.toMonth.DateTime;
                        sql_cmd.CommandText = "SELECT * FROM [444_MaterialPlan]"
                            + " WHERE (DATEPART(MM, DMonth) <= DATEPART(MM, @tomonth)"
                            + " AND DATEPART(yyyy, DMonth) = DATEPART(yyyy, @tomonth))"
                            + " OR DATEPART(yyyy, DMonth) < DATEPART(yyyy, @tomonth)"
                            + " ORDER BY [Dmonth] DESC";
                        sql_cmd.Parameters.Add("@tomonth", SqlDbType.DateTime).Value = tomonth.Date;
                    }
                }
                else
                {
                    if (string.IsNullOrEmpty(this.toMonth.EditValue.ToString()))
                    {
                        frommonth = this.fromMonth.DateTime;
                        sql_cmd.CommandText = "SELECT * FROM [444_MaterialPlan]"
                            + " WHERE (DATEPART(MM, DMonth) >= DATEPART(MM, @frommonth)"
                            + " AND DATEPART(yyyy, DMonth) = DATEPART(yyyy, @frommonth))"
                            + " OR DATEPART(yyyy, DMonth) > DATEPART(yyyy, @frommonth)"
                            + " ORDER BY [Dmonth] DESC";
                        sql_cmd.Parameters.Add("@frommonth", SqlDbType.DateTime).Value = frommonth.Date;
                    }
                    else
                    {
                        frommonth = this.fromMonth.DateTime;
                        tomonth = this.toMonth.DateTime;
                        sql_cmd.CommandText = "SELECT * FROM [444_MaterialPlan]"
                            + " WHERE ((DATEPART(MM, DMonth) >= DATEPART(MM, @frommonth)"
                            + " AND DATEPART(yyyy, DMonth) >= DATEPART(yyyy, @frommonth))"
                            + " OR DATEPART(yyyy, DMonth) > DATEPART(yyyy, @frommonth))"
                            + " AND ((DATEPART(MM, DMonth) <= DATEPART(MM, @tomonth)"
                            + " AND DATEPART(yyyy, DMonth) <= DATEPART(yyyy, @tomonth))"
                            + " OR DATEPART(yyyy, DMonth) < DATEPART(yyyy, @tomonth))"
                            + " ORDER BY [Dmonth] DESC";
                        sql_cmd.Parameters.Add("@frommonth", SqlDbType.DateTime).Value = frommonth.Date;
                        sql_cmd.Parameters.Add("@tomonth", SqlDbType.DateTime).Value = tomonth.Date;
                    }
                }
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    DataRow plan_row = this.PlanTable.NewRow();
                    plan_row["ID"] = Int32.Parse(dataReader["ID"].ToString());
                    plan_row["CompID"] = dataReader["CompID"].ToString().Trim();
                    plan_row["Quantity"] = float.Parse(dataReader["Quantity"].ToString().Trim());
                    plan_row["DMonth"] = DateTime.Parse(dataReader["DMonth"].ToString().Trim());
                    this.PlanTable.Rows.Add(plan_row);
                }
                dataReader.Close();
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
            }

            if (this.PlanTable.Rows.Count > 0)
            {
                gridF4MaterialImportPlan.DataSource = this.PlanTable;                
            }
            else
            {
                MessageBox.Show("There is no data for Import Plan");
                DataTable newTable = new DataTable();
                newTable.Columns.Add("ID", typeof(int));
                newTable.Columns.Add("CompID", typeof(string));                
                newTable.Columns.Add("Quantity", typeof(float));
                newTable.Columns.Add("DMonth", typeof(DateTime));                
                gridF4MaterialImportPlan.DataSource = newTable;
            }
            F4MaterialImportPlan.Columns[0].Caption = "ID";
            F4MaterialImportPlan.Columns[1].Caption = "CompID";
            F4MaterialImportPlan.Columns[2].Caption = "Quantity";
            F4MaterialImportPlan.Columns[3].Caption = "Month";
            F4MaterialImportPlan.Columns[3].DisplayFormat.FormatType = DevExpress.Utils.FormatType.DateTime;
            F4MaterialImportPlan.Columns[3].DisplayFormat.FormatString = "yyyy/MM";
            F4MaterialImportPlan.Columns[0].Visible = false;
            F4MaterialImportPlan.BestFitColumns();
        }

        ///////////////////////////////////
        /// Load Comp List
        ///////////////////////////////////
        private void LoadCompList()
        {
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT DISTINCT RobotNo FROM [231_Kosei] ORDER BY RobotNo";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Value = dataReader["RobotNo"].ToString().Trim();
                    item.Text = dataReader["RobotNo"].ToString().Trim();

                    this.compID.Properties.Items.Add(item);
                }
                dataReader.Close();
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot load Comp List");
            }
        }


        private void buttonAdd_Click(object sender, EventArgs e)
        {
            string err = null;
            string robot = null;            
            float quantity = 0;
            DateTime month = new DateTime();

            if (this.compID.SelectedIndex < 0)
            {
                err = "Please select Comp ID";
            }
            else
            {
                ComboboxItem item = (ComboboxItem)this.compID.SelectedItem;
                robot = item.Value.ToString();
            }

            if (string.IsNullOrEmpty(err))
            {
                if (string.IsNullOrEmpty(this.quantityValue.Text.Trim()))
                {
                    err = "Please enter Quantity";
                }
                else
                {
                    if (float.TryParse(this.quantityValue.Text.Trim(), out quantity))
                    {
                        if (quantity <= 0)
                        {
                            err = "Quantity must be greater than 0";
                        }
                    }
                    else
                    {
                        err = "Invalid Quantity.";
                    }
                }
            }

            if (string.IsNullOrEmpty(err))
            {
                if (string.IsNullOrEmpty(this.inputDate.EditValue.ToString()))
                {
                    err = "Please select Month";
                }
                else
                {
                    month = this.inputDate.DateTime;
                }
            }

            if (string.IsNullOrEmpty(err))
            {
                if (CheckExistPlan(robot, month))
                {
                    err = "The selected Comp has existed for plan of selected month.";
                }
            }

            if (string.IsNullOrEmpty(err))
            {
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "INSERT INTO [444_MaterialPlan] (CompID, Quantity, DMonth)"
                        + " VALUES ('" + robot + "', " + quantity + ", @inputdate)";
                    sql_cmd.Parameters.Add("@inputdate", SqlDbType.DateTime).Value = month.Date;
                    sql_cmd.ExecuteNonQuery();
                    sql_cmd.Dispose();
                    myConnection.Close();

                    LoadData();
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show("Cannot add new plan. Please try again.");
                }
            }
            else
            {
                MessageBox.Show(err);
            }           
        }

        /// <summary>
        /// Check exist plan in month
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private bool CheckExistPlan(string comp, DateTime month)
        {
            bool ret = false;
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT COUNT(ID) FROM [444_MaterialPlan]"
                    + " WHERE CompID='" + comp + "' AND DATEPART(MM, DMonth) = DATEPART(MM, @checkmonth)"
                    + " AND DATEPART(yyyy, DMonth) = DATEPART(yyyy, @checkmonth)";
                sql_cmd.Parameters.Add("@checkmonth", SqlDbType.DateTime).Value = month.Date;
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    if (dataReader[0] != DBNull.Value)
                    {
                        if (Int32.Parse(dataReader[0].ToString()) > 0)
                        {
                            ret = true;
                        }
                    }
                }
                dataReader.Close();
                sql_cmd.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ret = true;
            }
            return ret;
        }

        private void buttonCalculate_Click(object sender, EventArgs e)
        {
            switch (this.state)
            {
                case 0:                    
                    CalculateStore();
                    this.gridOrder.DataSource = null;
                    this.gridOrder.DataSource = this.OrderTable;
                    Order.Columns[0].Caption = "Part No";
                    Order.Columns[1].Caption = "Part No Purchase";
                    Order.Columns[2].Caption = "Part Name";
                    Order.Columns[3].Caption = "Supplier";
                    Order.Columns[4].Caption = "Need Quantity";
                    Order.Columns[5].Caption = "Remain Quantity";
                    Order.Columns[6].Caption = "In Order Quantity";
                    Order.Columns[7].Caption = "Unit ID";
                    Order.Columns[8].Caption = "Lead Time";
                    Order.Columns[9].Caption = "Packing Note";
                    Order.Columns[10].Caption = "Remark";
                    Order.Columns[11].Caption = "MOQ";
                    Order.Columns[12].Caption = "Marume";
                    Order.Columns[13].Caption = "RoHS";                    
                    Order.BestFitColumns();
                    this.gridOrder.BringToFront();
                    state = 1;
                    this.buttonCalculate.Text = "<< Back to Plan";
                    this.buttonDelete.Visible = false;

                    this.panelControl1.Visible = false;
                    this.fromMonth.Visible = false;
                    this.toMonth.Visible = false;
                    this.buttonPreview.Visible = false;
                    this.labelControl5.Visible = false;
                    this.labelControl6.Visible = false;
                    break;
                case 1:                    
                    this.gridF4MaterialImportPlan.DataSource = null;
                    LoadData();
                    this.gridF4MaterialImportPlan.BringToFront();
                    state = 0;

                    this.panelControl1.Visible = true;
                    this.buttonCalculate.Text = ">> Calculate Order";
                    this.panelControl1.Visible = true;
                    this.fromMonth.Visible = true;
                    this.toMonth.Visible = true;
                    this.buttonPreview.Visible = true;
                    this.labelControl5.Visible = true;
                    this.labelControl6.Visible = true;
                    this.buttonDelete.Visible = true;
                    break;
            }
        }

        //////////////////////////////////////////////
        /// Calculate store for Order
        /////////////////////////////////////////////
        private void CalculateStore()
        {
            this.StructureTable.Rows.Clear();
            this.SumTable.Rows.Clear();
            this.OrderTable.Rows.Clear();
            this.SubPlanTable.Rows.Clear();

            if (this.PlanTable.Rows.Count > 0)
            {                
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd;
                    SqlDataReader dataReader;
                    while (this.PlanTable.Rows.Count > 0)
                    {                        
                        foreach (DataRow plan_row in this.PlanTable.Rows)
                        {
                            sql_cmd = myConnection.CreateCommand();
                            sql_cmd.CommandText = "SELECT PartNo, Quantity, [Group] FROM [231_Kosei]"
                                + " WHERE (RobotNo='" + plan_row["CompID"].ToString().Trim() + "')";
                            dataReader = sql_cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                if (dataReader["Group"].ToString() != "0")
                                {
                                    DataRow structure_row = this.StructureTable.NewRow();
                                    structure_row["MaterialID"] = dataReader["PartNo"].ToString();
                                    float qty = 0;
                                    float kosei_qty = 0;
                                    float.TryParse(plan_row["Quantity"].ToString().Trim(), out qty);
                                    float.TryParse(dataReader["Quantity"].ToString().Trim(), out kosei_qty);
                                    structure_row["Quantity"] = qty * kosei_qty;
                                    this.StructureTable.Rows.Add(structure_row);
                                }
                                else
                                {
                                    DataRow subplan_row = this.SubPlanTable.NewRow();
                                    subplan_row["CompID"] = dataReader["PartNo"].ToString();
                                    float qty = 0;
                                    float kosei_qty = 0;
                                    float.TryParse(plan_row["Quantity"].ToString().Trim(), out qty);
                                    float.TryParse(dataReader["Quantity"].ToString().Trim(), out kosei_qty);
                                    subplan_row["Quantity"] = qty * kosei_qty;
                                    this.SubPlanTable.Rows.Add(subplan_row);
                                }
                            }
                            dataReader.Close();
                            sql_cmd.Dispose();
                        }
                        this.PlanTable.Rows.Clear();
                        this.PlanTable = this.SubPlanTable.Copy();
                        this.SubPlanTable.Rows.Clear();
                    }
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show("Structure Error: " + ex.Message);
                }
                                
                //////////////////////////////////////////
                /// Sum Structure Table
                //////////////////////////////////////////
                if (this.StructureTable.Rows.Count > 0)
                {
                    foreach (DataRow detail_row in this.StructureTable.Rows)
                    {
                        DataRow[] sum_rows = this.SumTable.Select("MaterialID='" + detail_row["MaterialID"].ToString() + "'");
                        if (sum_rows.Length > 0)
                        {
                            foreach (DataRow edit_row in sum_rows)
                            {
                                edit_row.BeginEdit();
                                float current_quantity = float.Parse(edit_row["Quantity"].ToString());
                                float add_quantity = float.Parse(detail_row["Quantity"].ToString());
                                edit_row["Quantity"] = current_quantity + add_quantity;
                                edit_row.EndEdit();
                            }
                        }
                        else
                        {
                            DataRow new_sum = SumTable.NewRow();
                            new_sum["MaterialID"] = detail_row["MaterialID"].ToString();
                            new_sum["Quantity"] = float.Parse(detail_row["Quantity"].ToString());
                            SumTable.Rows.Add(new_sum);                            
                        }
                    }
                }

                //////////////////////////////////////////
                /// Get Store
                //////////////////////////////////////////                
                if (this.SumTable.Rows.Count > 0)
                {                    
                    try
                    {
                        myConnection.Open();
                        SqlCommand sql_cmd2;
                        SqlDataReader dataReader2;
                        foreach (DataRow sum_row in this.SumTable.Rows)
                        {
                            float need_qty = float.Parse(sum_row["Quantity"].ToString());
                            sql_cmd2 = myConnection.CreateCommand();
                            sql_cmd2.CommandText = "SELECT t1.PartNo, t1.MinQuantity, t1.Marume, t1.ROHS, t4.SupplierName,"
                                + " t1.Remark, t1.PackingNote, t2.RemainQuantity, t3.InOrderQuantity, t1.LeadTime,"
                                + " t1.UnitID, t1.PartEnglishName, t1.PartNoPurchase"
                                + " FROM [222_Part] AS t1 LEFT JOIN"
                                + " [VF4MaterialStore] AS t2 ON t1.PartNo=t2.MaterialID LEFT JOIN"
                                + " [VF4MaterialNotReceive] AS t3 ON t1.PartNo=t3.MaterialID LEFT JOIN"
                                + " [222_Supplier] AS t4 ON t1.SupplierID=t4.SupplierID"
                                + " WHERE t1.PartNo='" + sum_row["MaterialID"].ToString().Trim() + "'";                                
                            dataReader2 = sql_cmd2.ExecuteReader();
                            while (dataReader2.Read())
                            {
                                DataRow order_row = this.OrderTable.NewRow();
                                order_row["PartNo"] = dataReader2["PartNo"].ToString();
                                order_row["PartNoPurchase"] = dataReader2["PartNoPurchase"].ToString();
                                order_row["PartName"] = dataReader2["PartEnglishName"].ToString();
                                order_row["Supplier"] = dataReader2["SupplierName"].ToString();
                                order_row["NeedQuantity"] = need_qty;
                                float remain_qty = 0;
                                float.TryParse(dataReader2["RemainQuantity"].ToString().Trim(), out remain_qty);
                                order_row["RemainQuantity"] = remain_qty;
                                float inorder_qty = 0;
                                float.TryParse(dataReader2["InOrderQuantity"].ToString().Trim(), out inorder_qty);
                                order_row["InOrderQuantity"] = inorder_qty;
                                if (!string.IsNullOrEmpty(dataReader2["UnitID"].ToString().Trim()))
                                {
                                    if (((int)(dataReader2["UnitID"].ToString().Trim()[0])) == 65402)
                                    {
                                        order_row["UnitID"] = "PC";
                                    }
                                    else
                                    {
                                        order_row["UnitID"] = dataReader2["UnitID"].ToString();
                                    }
                                }
                                
                                int leadtime = 0;
                                Int32.TryParse(dataReader2["LeadTime"].ToString().Trim(), out leadtime);
                                order_row["LeadTime"] = leadtime;
                                order_row["PackingNote"] = dataReader2["PackingNote"].ToString();
                                order_row["Remark"] = dataReader2["Remark"].ToString();

                                float moq = 0;
                                float.TryParse(dataReader2["MinQuantity"].ToString().Trim(), out moq);
                                order_row["MOQ"] = moq;
                                float marume = 0;
                                float.TryParse(dataReader2["Marume"].ToString().Trim(), out marume);
                                order_row["Marume"] = marume;
                                int rohs = 0;
                                Int32.TryParse(dataReader2["ROHS"].ToString().Trim(), out rohs);
                                if (rohs > 0)
                                {
                                    order_row["RoHS"] = true;
                                }
                                else
                                {
                                    order_row["RoHS"] = false;
                                }                                
                                this.OrderTable.Rows.Add(order_row);
                            }
                            dataReader2.Close();
                            sql_cmd2.Dispose();
                        }
                        myConnection.Close();
                    }
                    catch (Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        MessageBox.Show("Store error: " + ex.Message);
                    }
                }
            }
        }

        private void buttonDelete_Click(object sender, EventArgs e)
        {
            deleteRecord();
        }

        private void deleteRecord()
        {
            int[] rows = F4MaterialImportPlan.GetSelectedRows();
            if (rows.Length <= 0)
            {
                MessageBox.Show("There is no selected record to delete");
            }
            else
            {
                if (MessageBox.Show("Are you really to delete selected records now?", "Confirm delete", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    string[] id_list = new string[rows.Length];
                    for (int i = 0; i < rows.Length; i++)
                    {
                        DataRow selected_row = F4MaterialImportPlan.GetDataRow(rows[i]);
                        id_list[i] = "'" + selected_row[0].ToString() + "'";
                    }
                    string id = string.Join(",", id_list);

                    try
                    {
                        myConnection.Open();
                        string sql = "DELETE [444_MaterialPlan] WHERE ID IN (" + id + ")";
                        SqlCommand cmd_sql = new SqlCommand(sql, myConnection);
                        cmd_sql.ExecuteNonQuery();
                        cmd_sql.Dispose();
                        myConnection.Close();
                        MessageBox.Show("Plans deleted success.");
                        LoadData();
                    }
                    catch //(Exception ex)
                    {
                        if (myConnection.State == ConnectionState.Open)
                        {
                            myConnection.Close();
                        }
                        //MessageBox.Show(ex.Message);
                        MessageBox.Show("Cannot delete Plans. Please try again.");
                    }
                }
            }
        }

        private void buttonPreview_Click(object sender, EventArgs e)
        {
            LoadData();
        }
    }
}
